package edu.fub.pub2search.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Iterator;

import javax.sql.rowset.FilteredRowSet;
import javax.sql.rowset.Predicate;

import com.sun.rowset.FilteredRowSetImpl;
import com.sun.rowset.internal.Row;

public class DataAccess {
	
	/* MySQL connection */
	private Connection conn;
		
	/* Constants */
	public static final int PUB_NAME = 1;
	public static final int PUB_DESCRIPTION = 2;
	public static final int PUB_FACILITY = 3;
	public static final int PUB_OPEN_HOUR = 4;
	public static final int PUB_CLOSE_HOUR = 5;
	public static final int PUB_LATITUDE = 6;
	public static final int PUB_LONGITUDE = 7;
	
	/* The constructor */
	public DataAccess(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * Queries the pubs within the given coordinates. Moreover, 
	 * the result is filtered by user's predicate. 
	 * 
	 * @param x1 The first latitude.
	 * @param x2 The second latitude. 
	 * @param y1 The first longitude.
	 * @param y2 The second longitude.
	 * @param filter A <i>Predicate</i> for further selection.
	 * @return A collection of pubs data.
	 */
	public Collection<?> queryPub(int x1, int x2, int y1, int y2, Predicate filter) {
		
		/* 
		 * NOTE: There are some important constraints, that are:
		 *  - lat1 should be bigger than lat2
		 *  - lng1 should be bigger than lng2 
		 */
		int lat1, lat2, lng1, lng2;
		
		if (x1 >= x2) { lat1 = x1; lat2 = x2; }
		else { lat1 = x2; lat2 = x1; }
		
		if (y1 >= y2) { lng1 = y1; lng2 = y2; }
		else { lng1 = y2; lng2 = y1; }
		
		// Begin querying
		Collection<?> result = null;
		
		try {
			String query = "SELECT name, description, facility, open_hour, close_hour, latitude, longitude " +
						   "FROM Pub " +
						   "WHERE latitude <= ? AND latitude >= ? " + 
						   "AND longitude <= ? AND longitude >= ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			
			stmt.setInt(1, lat1);
			stmt.setInt(2, lat2);
			stmt.setInt(3, lng1);
			stmt.setInt(4, lng2);
			
			ResultSet rs = stmt.executeQuery();
			FilteredRowSet frs = new FilteredRowSetImpl();
			frs.populate(rs);		
			frs.setFilter(filter);
			
			result = frs.toCollection();  // store the filtered result in a collection.

			rs.close();  // release the rowset objects, we don't need them anymore.
		 	frs.close();
		 	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return result;
	}
	
	/**
	 * Queries the pubs within the given coordinates. 
	 * 
	 * @param x1 The first latitude.
	 * @param x2 The second latitude. 
	 * @param y1 The first longitude.
	 * @param y2 The second longitude.
	 * @return A collection of pubs data.
	 */
	public Collection<?> queryPub(int x1, int x2, int y1, int y2) {
			
		return queryPub(x1, x2, y1, y2, new EmptyFilter());
		
	}
	
	/**
	 * Get an array of <i>Pub</i> within the given coordinates. Moreover, 
	 * the result is filtered by user's predicate. 
	 * 
	 * @param x1 The first latitude.
	 * @param x2 The second latitude. 
	 * @param y1 The first longitude.
	 * @param y2 The second longitude.
	 * @param filter A <i>Predicate</i> for further selection.
	 * @return An array of <i>Pub</i>.
	 */
	public Pub[] getPub(int x1, int x2, int y1, int y2, Predicate filter) { 
	
		Collection<?> result = queryPub(x1, x2, y1, y2, filter);
	
		return collectResultToArrayOfPub(result);
	}
	
	/**
	 * Get an array of <i>Pub</i> within the given coordinates. 
	 * 
	 * @param x1 The first latitude.
	 * @param x2 The second latitude. 
	 * @param y1 The first longitude.
	 * @param y2 The second longitude.
	 * @return An array of <i>Pub</i>.
	 */
	public Pub[] getPub(int x1, int x2, int y1, int y2) {
		
		Collection<?> result = queryPub(x1, x2, y1, y2, new EmptyFilter());
		
		return collectResultToArrayOfPub(result);
	}
	
// ---------------- PRIVATE METHODS ----------------
	
	/**
	 * Organize the database query into an array of <i>Pub</i> data model.
	 */
	private Pub[] collectResultToArrayOfPub(Collection<?> result) {
		
		int count = result.size();
		int counter = 0;
		Pub[] pubList = new Pub[count];
		
		try {
			Iterator<?> iter = result.iterator();
			while(iter.hasNext()) {
				Time time = null;
				SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
				
				Row entry = (Row) iter.next();
				Pub pub = new Pub();
				
				pub.setName((String)entry.getColumnObject(PUB_NAME)); // name
				pub.setDescription((String) entry.getColumnObject(PUB_DESCRIPTION)); // description
				pub.setFacility((String) entry.getColumnObject(PUB_FACILITY));	// facility	
				
				time = (Time) entry.getColumnObject(PUB_OPEN_HOUR); // opening hour
				pub.setOpenHour(new StringBuilder(timeFormat.format(time)).toString());
				
				time = (Time) (Time) entry.getColumnObject(PUB_CLOSE_HOUR); // closing hour
				pub.setCloseHour(new StringBuilder(timeFormat.format(time)).toString());
				
				pub.setLatitude((Integer) entry.getColumnObject(PUB_LATITUDE)); // latitude
				pub.setLongitude((Integer) entry.getColumnObject(PUB_LONGITUDE)); // longitude
				
				pubList[counter++] = pub;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return pubList;
	}
}
